package com.plug.common.jdbc;

import org.apache.commons.beanutils.BeanUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * Created by slaye on 2016/12/16.
 */
public class JDBCBaseDao {

    final Logger log = LoggerFactory.getLogger(getClass());

    private DBConnectionManager dbcm = DBConnectionManager.getInstance();

    private void realease(Statement stmt, ResultSet rs, String dbName, Connection conn){
        if (rs != null){
            try {
                rs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        if (stmt != null){
            try {
                stmt.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        dbcm.freeConnection(dbName, conn);
    }

    /* (non-Javadoc)
     */
    public int execSqlCount(String dbName, String sql) {
        log.debug("execSqlCount : " + sql);
        int count = 0;
        Statement stmt = null;
        ResultSet rs = null;
        Connection conn = dbcm.getConnection(dbName);
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);
            while (rs.next()) {
                count = rs.getInt(1);
            }
        } catch (SQLException e) {
            log.error("execSqlCount error: " + e);
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            realease(stmt, rs, dbName, conn);
        }
        return count;
    }

    public int execSqlCount(String sql, Object[] obj) {
        return this.execSqlCount("plug", sql, obj);
    }

    /* (non-Javadoc)
     */
    public int execSqlUpdate(String dbName, String sql) {
        log.debug("execSqlUpdate : " + sql);
        int count = 0;
        Connection conn = dbcm.getConnection(dbName);
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            count = stmt.executeUpdate(sql);
        } catch (SQLException e) {
            log.error("execSqlUpdate error: " + e.getMessage());
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            realease(stmt, null, dbName, conn);
        }
        return count;
    }


    /* (non-Javadoc)
     */
    public int execPsmtSqlUpdate(String dbName, String pstSql, Object[] obj) {
        int count = 0;
        Connection conn = dbcm.getConnection(dbName);
        PreparedStatement pstmt=null;
        try {
            pstmt= conn.prepareStatement(pstSql);
            for (int i= 0; i< obj.length; i++){
                pstmt.setObject(i+1, obj[i]);
            }
            count = pstmt.executeUpdate();
        } catch (SQLException e) {
            log.error("execSqlUpdate error: " + e.getMessage());
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            realease(pstmt, null, dbName, conn);
        }
        return count;
    }

    /* (non-Javadoc)
     */
    public List execSqlEntityQuery(String dbName, String sql, Object obj) {
        log.debug("execSqlEntityQuery : " + sql);
        List list = new ArrayList();
        Connection conn = dbcm.getConnection(dbName);
        ResultSet rs = null;
        Statement stmt = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
            int colCount = metaData.getColumnCount(); // 取得所有列的个数
            // 存放返回结果的容器
            Field[] fields = obj.getClass().getDeclaredFields(); // 取得业务对象的属性
            while (rs.next()) {
                Object newInstance = obj.getClass().newInstance(); // 构造业务对象实例
                // 将结果集中每一条记录，每一个字段取出，根据命名规则，对对应的业务对象的属性进行赋值
                for (int i = 1; i <= colCount; i++) { // 对于该记录的每一列
                    Object value = rs.getObject(i);
                    for (int j = 0; j < fields.length; j++) {
                        Field f = fields[j];
                        if (f.getName().equalsIgnoreCase(metaData.getColumnName(i).replaceAll("_", ""))) {
                            BeanUtils.copyProperty(newInstance, f.getName(), value);
                        }
                    }
                }
                list.add(newInstance);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            realease(stmt, rs, dbName, conn);
        }
        return list;
    }

    /* (non-Javadoc)
     * @see com.gari.common.dao.IJDBCBaseDao#execSqlBatch(java.lang.String, java.util.List)
     */
    public void execSqlBatch(String dbName, List<String> sqls) {
        log.debug("execSqlBatch :  batchSize is" + sqls.size());
        Connection conn = dbcm.getConnection(dbName);
        Statement stmt = null;
        try {
            conn.setAutoCommit(false);
            stmt = conn.createStatement();

            for (int i = 0; i < sqls.size(); i++) {
                stmt.addBatch(sqls.get(i));
            }

            try {
                stmt.executeBatch();
                conn.commit();
            } catch (SQLException e) {
                e.printStackTrace();
                conn.rollback();
                throw new RuntimeException(e);
            }

            conn.setAutoCommit(true);
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            realease(stmt, null, dbName, conn);
        }

    }

    /* (non-Javadoc)
     */
    public List<Map<String, Object>> execSqlQuery(String dbName, String sql) {
        log.debug("execSqlQuery : " + sql);
        int count = 0;
        List list = new ArrayList<Map>();

        Connection conn = dbcm.getConnection(dbName);
        Statement stmt = null;
        ResultSet rs = null;
        try {
            stmt = conn.createStatement();
            rs = stmt.executeQuery(sql);

            ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
            int colCount = metaData.getColumnCount(); // 取得所有列的个数
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                // 将结果集中每一条记录，每一个字段取出，根据命名规则，对对应的业务对象的属性进行赋值
                for (int i = 1; i <= colCount; i++) { // 对于该记录的每一列
                    Object value = rs.getObject(i);
                    map.put(metaData.getColumnName(i), value);
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            realease(stmt, rs, dbName, conn);
        }
        return list;
    }

    /* (non-Javadoc)
     */
    public void execSqlBatch(List<String> sqls) {
        this.execSqlBatch("plug", sqls);
    }

    /* (non-Javadoc)
     */
    public int execSqlCount(String sql) {
        return this.execSqlCount("plug", sql);
    }

    /* (non-Javadoc)
     */
    public List execSqlEntityQuery(String sql, Object obj) {
        return this.execSqlEntityQuery("plug", sql, obj);
    }

    /* (non-Javadoc)
     */
    public List<Map<String, Object>> execSqlQuery(String sql) {
        return this.execSqlQuery("plug", sql);
    }

    /* (non-Javadoc)
     */
    public int execSqlUpdate(String sql) {
        return this.execSqlUpdate("plug", sql);
    }

    public int execPsmtSqlUpdate(String sql, Object[] obj) {
        return this.execPsmtSqlUpdate("plug", sql, obj);
    }

    public void execSqlBatchs(List<Map<String, Object[]>> params) {
        this.execSqlBatchs("plug",params);
    }

    public List execSqlEntityQuery(String sql, Object obj, Object[] obj2) {
        return this.execSqlEntityQuery( "plug", sql, obj, obj2);
    }

    public List<Map<String, Object>> execSqlQuery(String sql, Object[] obj) {
        return this.execSqlQuery( "plug", sql, obj);
    }

    public int execSqlUpdate(String sql, Object[] obj) {
        return this.execSqlUpdate( "plug", sql, obj);
    }

    /**
     * 函数功能说明:获取数据连接池管理类
     *
     * @函数参数:
     * @return
     * @return DBConnectionManager 连接池管理类
     */
    protected DBConnectionManager getDbcm() {
        return dbcm;
    }

    public int execSqlCount(String dbname,String sql, Object[] obj) {
        log.debug("execSqlCount : " + sql);
        int count = 0;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Connection conn = dbcm.getConnection(dbname);
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                ps.setObject(i+1, obj[i]);
            }
            rs = ps.executeQuery();
            while (rs.next()) {
                count = rs.getInt(1);
            }

        } catch (SQLException e) {
            log.error("execSqlCount error: " + e);
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            realease(ps, rs, dbname, conn);
        }
        return count;
    }

    public int execSqlUpdate(String dbName, String sql, Object[] obj) {
        log.debug("execSqlUpdate : " + sql);
        int count = 0;
        Connection conn = dbcm.getConnection(dbName);
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                ps.setObject(i+1, obj[i]);
            }
            count = ps.executeUpdate();
        } catch (SQLException e) {
            log.error("execSqlUpdate error: " + e.getMessage());
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            realease(ps, null, dbName, conn);
        }
        return count;
    }

    public void execSqlBatchs(String dbName, List<Map<String, Object[]>> params) {
        log.debug("execSqlBatch :  batchSize is" + params.size());
        Connection conn = dbcm.getConnection(dbName);
        PreparedStatement ps = null;
        try {
            for (int i = 0; i < params.size(); i++) {
                Map<String, Object[]> map = params.get(i);
                for (String key : map.keySet()) {
                    ps = conn.prepareStatement(key);
                    Object[] obj = map.get(key);
                    for (int j = 0; j < obj.length; j++) {
                        ps.setObject(j+1, obj[j]);
                    }
                    ps.executeUpdate();
                    ps.close();
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            realease(ps, null, dbName, conn);
        }
    }

    public List execSqlEntityQuery(String dbName,String sql, Object obj, Object[] obj_) {
        log.debug("execSqlEntityQuery : " + sql);
        List list = new ArrayList();
        ResultSet rs = null;
        PreparedStatement ps = null;
        Connection conn = dbcm.getConnection(dbName);
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < obj_.length; i++) {
                ps.setObject(1+i, obj_[i]);
            }
            rs = ps.executeQuery();

            ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
            int colCount = metaData.getColumnCount(); // 取得所有列的个数
            // 存放返回结果的容器
            Field[] fields = obj.getClass().getDeclaredFields(); // 取得业务对象的属性
            while (rs.next()) {
                Object newInstance = obj.getClass().newInstance(); // 构造业务对象实例
                // 将结果集中每一条记录，每一个字段取出，根据命名规则，对对应的业务对象的属性进行赋值
                for (int i = 1; i <= colCount; i++) { // 对于该记录的每一列
                    Object value = rs.getObject(i);
                    for (int j = 0; j < fields.length; j++) {
                        Field f = fields[j];
                        if (f.getName().equalsIgnoreCase(metaData.getColumnName(i).replaceAll("_", ""))) {
                            BeanUtils.copyProperty(newInstance, f.getName(), value);
                        }
                    }
                }
                list.add(newInstance);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            realease(ps, rs, dbName, conn);
        }
        return list;
    }

    public List<Map<String, Object>> execSqlQuery(String dbName, String sql,
                                                  Object[] obj) {
        log.debug("execSqlQuery : " + sql);
        List list = new ArrayList<Map>();
        Connection conn = dbcm.getConnection(dbName);
        PreparedStatement ps = null;
        ResultSet rs = null;
        try {
            ps = conn.prepareStatement(sql);
            for (int i = 0; i < obj.length; i++) {
                ps.setObject(i+1, obj[i]);
            }
            rs = ps.executeQuery();

            ResultSetMetaData metaData = rs.getMetaData(); // 取得结果集的元元素
            int colCount = metaData.getColumnCount(); // 取得所有列的个数
            while (rs.next()) {
                Map<String, Object> map = new HashMap<String, Object>();
                // 将结果集中每一条记录，每一个字段取出，根据命名规则，对对应的业务对象的属性进行赋值
                for (int i = 1; i <= colCount; i++) { // 对于该记录的每一列
                    Object value = rs.getObject(i);
                    map.put(metaData.getColumnName(i), value);
                }
                list.add(map);
            }
        } catch (SQLException e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException(e);
        } finally {
            realease(ps, rs, dbName, conn);
        }
        return list;
    }
    public static void close(Connection conn, PreparedStatement ps, ResultSet rs){
        try {
            if(rs != null) {
                rs.close();
            }
            if(ps != null){
                rs.close();
            }
            if(conn != null){
                conn.close();
            }
        }catch (Exception e){
            e.printStackTrace();
        }
    }
}
